package com.jwm.platform.db;

import cn.hutool.core.bean.BeanUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 崔洁
 */
@Component
public class BaseDao {
    private static final Logger logger = LoggerFactory.getLogger(Warning.class);
    @Resource
    private JdbcTemplate jdbcTemplate;

    public int addBase(final BaseModel model) throws Exception {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(conn -> {
            String sql = " INSERT INTO ";
            String strFields = "";
            String strValues = "";
            modelAttribute matt = model.getClass().getAnnotation(modelAttribute.class);
            sql += matt.name() + " (";
            Field[] fields = model.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                fieldAttribute fatt = fields[i].getAnnotation(fieldAttribute.class);
                if (fatt.primaryKey()) {
                    continue;
                }
                String fname = fatt.name();
                strFields += ",";
                strValues += ",";
                strFields += fname;
                strValues += "?";
            }
            sql += strFields.substring(1);
            sql += ") values (";
            sql += strValues.substring(1);
            sql += ")";
            PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < fields.length; i++) {
                fieldAttribute fatt = fields[i].getAnnotation(fieldAttribute.class);
                if (fatt.primaryKey()) {
                    continue;
                }
                Object fvalue;
                fields[i].setAccessible(true);
                try {
                    fvalue = fields[i].get(model);
                    if (fvalue != null && fvalue.getClass().equals(Date.class)) {
                        Date date = (Date) fvalue;
                        java.sql.Timestamp sqlDate = new java.sql.Timestamp(date.getTime());
                        ps.setObject(i, sqlDate);
                    } else {
                        if (fvalue == null && !fields[i].getType().equals(Date.class)) {
                            fvalue = "";
                        }
                        if (fvalue == null && fields[i].getType().equals(BigDecimal.class)) {
                            fvalue = "0";
                        }
                        if (fatt.length() > 0) {
                            fvalue = padLeft(fvalue.toString(), fatt.length(), '0');
                        }
                        ps.setObject(i, fvalue);
                    }
                } catch (IllegalAccessException e) {
                    logger.error("插入失败\n{}", e.getMessage());
                }
            }
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public int modifyBase(BaseModel model) throws Exception {
        List<Object> list = new ArrayList<>();
        StringBuilder where = new StringBuilder();
        StringBuilder sql = new StringBuilder();
        StringBuilder setSql = new StringBuilder();

        sql.append(" UPDATE ");
        modelAttribute matt = model.getClass().getAnnotation(modelAttribute.class);
        String tableName = matt.name();
        sql.append(tableName);
        sql.append(" SET ");
        Field[] fields = model.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            fieldAttribute fatt = fields[i].getAnnotation(fieldAttribute.class);
            if (fatt.primaryKey() || !fatt.allowupdate()) {
                continue;
            }
            Object fvalue = "";
            fields[i].setAccessible(true);
            fvalue = fields[i].get(model);
            String fname = fatt.name();
            setSql.append(",");
            setSql.append(fname);
            setSql.append("=?");
            list.add(fvalue);
        }
        where.append(" WHERE 1=2 ");
        for (int i = 0; i < fields.length; i++) {
            fieldAttribute fatt = fields[i].getAnnotation(fieldAttribute.class);
            if (fatt.primaryKey()) {
                Object fvalue = "";
                fields[i].setAccessible(true);
                fvalue = fields[i].get(model);
                String fname = fatt.name();
                where.append(" OR ");
                where.append(fname);
                where.append("=?");
                list.add(fvalue);
            }
        }

        sql.append(setSql.substring(1));
        sql.append(where);
        int r = jdbcTemplate.update(sql.toString(), list.toArray());

        return r;
    }

    public int deleteBase(Class c, int id) {
        if (sqlValidate(String.valueOf(id))) {
            return 0;
        }
        modelAttribute matt = (modelAttribute) c.getAnnotation(modelAttribute.class);
        String tableName = matt.name();
        String sql = "delete from  " + tableName + " WHERE id=?";
        Object[] args = new Object[1];
        args[0] = id;
        return jdbcTemplate.update(sql, args);
    }

    public <C> List<C> queryList(C c) {
        modelAttribute matt = c.getClass().getAnnotation(modelAttribute.class);
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from " + matt.name());
        return (List<C>) BeanUtil.copyToList(list, c.getClass());
    }

    public <C> C queryById(C c, int id) {
        if (sqlValidate(String.valueOf(id))) {
            return null;
        }
        modelAttribute matt = c.getClass().getAnnotation(modelAttribute.class);
        Map<String, Object> map = jdbcTemplate.queryForMap("SELECT * FROM " + matt.name() + " WHERE id=" + id);
        return BeanUtil.fillBeanWithMap(map, c, true);
    }

    // 效验sql注入
    protected static boolean sqlValidate(Object[] str) {
        if (str == null) {
            return false;
        }
        for (Object s : str) {
            if (sqlValidate(String.valueOf(s))) {
                return true;
            }
        }
        return false;
    }

    protected static boolean sqlValidate(String[] str) {
        if (str == null) {
            return false;
        }
        for (String s : str) {
            if (sqlValidate(s)) {
                return true;
            }
        }
        return false;
    }

    protected static boolean sqlValidate(String str) {
        if (str == null) {
            return false;
        }
        str = str.toLowerCase();
        String badStr = "select|update|delete|exec|count|drop|execute|insert|create|--|from|regxp|having";
        String[] badStrs = badStr.split("\\|");
        for (int i = 0; i < badStrs.length; i++) {
            if (str.indexOf(badStrs[i] + " ") >= 0) {
                return true;
            }
        }
        return false;
    }

    public static String padLeft(String oriStr, int len, char alexin) {
        String str = "";
        int strlen = oriStr.length();
        if (strlen < len) {
            for (int i = 0; i < len - strlen; i++) {
                str = str + alexin;
            }
        }
        str = str + oriStr;
        return str;
    }
}
